【MySQL】UNIQUE制約 - カラムのユニーク（一意）化

MySQLのUNIQUE制約について解説します。

検証環境

UNIQUE制約

UNIQUE制約は“カラムの値を一意に保つ制約”です。

任意のカラムに設定でき、複数のカラムの組み合わせにすることも可能です。

この制約を設定したカラムの値は重複できず、インデックスが自動で作成されます。

主キーのPRIMARY KEY制約と似ていますが、使用目的が異なります。

また、PRIMARY KEY制約は値のNULLが許可されませんが、UNIQUEはNULLの設定が可能です。

単体カラム

単体カラムにUNIQUE制約を設定できます。

基本構文

カラム定義 UNIQUE

カラム定義に続いて、UNIQUEを記述します。

サンプル

Command Line

mysql> /* テーブル作成 */
mysql> CREATE TABLE items (
    ->     id INT UNIQUE,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> /* テーブル情報 */
mysql> DESC items;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| price | int         | YES  |     | NULL    |       |
| stock | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> /* インデックス確認 */
mysql> SHOW INDEX FROM items;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | id       |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)

mysql> /* レコード追加 */
mysql> INSERT INTO items VALUE ( 1, 'Apple', 200, 10 );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO items VALUE ( 1, 'Orange', 150, 3 );
ERROR 1062 (23000): Duplicate entry '1' for key 'items.id'
mysql> INSERT INTO items VALUE ( 2, 'Pineapple', 1100, 3 );
Query OK, 1 row affected (0.00 sec)

ユニークなカラムはDESCで表示したテーブル情報のKey項目にMULが表示されます。

また、値の重複ができないため、２回目のレコード追加はエラーが発生しています。

複数カラム

複数カラムの組み合わせにUNIQUE制約を設定できます。

基本構文

CREATE TABLE テーブル名 (
    カラム定義1,
    カラム定義2,
    カラム定義3,
    UNIQUE( カラム1, カラム2 )
);

テーブル定義にUNIQUE(カラム１, カラム２)を記述します。

サンプル

Command Line

mysql> /* テーブル作成 */
mysql> CREATE TABLE items (
    ->     id INT,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT,
    ->     UNIQUE( id, name )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> /* テーブル情報 */
mysql> DESC items;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| price | int         | YES  |     | NULL    |       |
| stock | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> /* インデックス確認 */
mysql> SHOW INDEX FROM items;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | id       |            1 | id          | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| items |          0 | id       |            2 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

mysql> /* レコード追加 */
mysql> INSERT INTO items VALUE ( 1, 'Apple', 200, 10 );
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO items VALUE ( 1, 'Orange', 150, 3 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items VALUE ( 2, 'Apple', 250, 10 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items VALUE ( 1, 'Apple', 200, 10 );
ERROR 1062 (23000): Duplicate entry '1-Apple' for key 'items.id'

idとnameのカラムをユニークに設定しています。

複数カラムの場合、テーブル情報のKey項目には、最初のカラムのみMULが表示されます。

組み合わせの重複できませんが、一方のみのは許可されるので、２、３回目のレコード追加は正常に処理されます。

４回目の追加はidとnameの組み合わせが重複したためエラーが発生しました。

ユニークカラムの確認

上記で述べたようにDESCを使ったテーブル情報では、複数カラムのUNIQUE制約の場合、最初のカラムしか分かりません。

正確なユニークカラムを確認するにはSHOW CREATE TABLEを使って確認できます。

mysql> SHOW CREATE TABLE items;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| items | CREATE TABLE `items` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `price` int DEFAULT NULL,
  `stock` int DEFAULT NULL,
  UNIQUE KEY `id` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意点

UNIQUE制約のカラムは主キー（PRIMARY KEY）と異なり、NULLが許容されます。
値がNULLの場合、重複していてもエラーを発生せず、レコードが追加されることに注意してください。

mysql> /* テーブル作成 */
mysql> CREATE TABLE items (
    ->     id INT,
    ->     name VARCHAR(20),
    ->     price INT,
    ->     stock INT,
    ->     UNIQUE( id, name )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO items VALUE ( NULL, 'Apple', 200, 10 );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO items VALUE ( NULL, 'Apple', 200, 10 );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM items;
+------+-------+-------+-------+
| id   | name  | price | stock |
+------+-------+-------+-------+
| NULL | Apple |   200 |    10 |
| NULL | Apple |   200 |    10 |
+------+-------+-------+-------+
2 rows in set (0.00 sec)